/**
 * 
 */
package edu.caltech.android.database;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

/**
 * Database adapter class, encapsulating SQLite database. Patterned after
 * example in Reto Meier's "Professional Android 2 Application Development"
 * 
 * I've had some problems with
 * "java.lang.IllegalStateException: database not open", particularly if I quit
 * the app and then quickly restart it. A possible solution or work-around is to
 * explicitly check db.isOpen before using the database. Matt, Dec. 14
 * 
 * @author mfaulk
 * 
 */
public class MeasurementDBAdapter {
	private static final String TAG = "MeasurementDBAdapter";

	private static final String DATABASE_NAME = "measurements.db";
	private static final String DATABASE_TABLE = "mainTable";
	private static final int DATABASE_VERSION = 1;

	// The index (key) column name for use in where clauses.
	public static final String KEY_ID = "_id";

	// The name and column index of each column in your database.
	public static final String KEY_START_TIME = "startTime";
	public static final String KEY_END_TIME = "endTime";
	public static final String KEY_FILE_NAME = "fileName";

	// SQL Statement to create a new database.
	private static final String DATABASE_CREATE = "create table "
			+ DATABASE_TABLE + " (" + KEY_ID
			+ " integer primary key autoincrement, " + KEY_START_TIME
			+ " long, " + KEY_END_TIME + " long, " + KEY_FILE_NAME
			+ " text not null);";

	// Variable to hold the database instance
	private SQLiteDatabase db;

	// Context of the application using the database.
	private final Context context;

	// Database open/upgrade helper
	private myDbHelper dbHelper;

	public MeasurementDBAdapter(Context _context) {
		context = _context;
		dbHelper = new myDbHelper(context, DATABASE_NAME, null,
				DATABASE_VERSION);
	}

	public MeasurementDBAdapter open() throws SQLException {
		Log.v(TAG, "opening");
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		Log.v(TAG, "closing");
		db.close();
	}

	// public int size(){
	// // TODO
	// }

	/**
	 * 
	 * @param fileName
	 * @param startTime
	 *            - first sample time (milliseconds UTC)
	 * @param endTime
	 *            - last sample time (milliseconds UTC)
	 * 
	 * @throws IOException
	 */
	public void insert(String fileName, long startTime, long endTime)
			throws IOException {

		ContentValues newValues = new ContentValues();

		// Assign values for each row.
		newValues.put(KEY_START_TIME, startTime);
		newValues.put(KEY_END_TIME, endTime);
		newValues.put(KEY_FILE_NAME, fileName);

		// TODO was receiving illegalStateExceptions: database not open. Some
		// websites suggest this may have to do with screen orientation changes?
		//
		if (!db.isOpen()) {
			Log.d(TAG, "database not open for insert, attempting to open");

			try {
				open();
			} catch (SQLException e) {
				Log.getStackTraceString(e);
			}
		}

		// Insert the row into table
		Log.v(TAG, "inserting new measurements into database");
		long rowID = db.insert(DATABASE_TABLE, null, newValues);
		if (rowID == -1) {
			Log.d(TAG, "SQLite row insert error");
		}
	}

	/**
	 * Get all fileNames of logs which intersect the time interval [startTime,
	 * endTime]
	 * 
	 * TODO: perhaps this should return a cursor?
	 * 
	 * @param startTime
	 *            (milliseconds UTC)
	 * @param endTime
	 *            (milliseconds UTC)
	 * @return
	 */
	public List<String> getIntersectingLogfiles(long startTime, long endTime) {
		ArrayList<String> entries = new ArrayList<String>();

		if (!db.isOpen()) {
			Log.d("TAG", "database not open to get entries");
			return entries;
		} else {
			Log.v(TAG, "performing database range query");

			String start = Long.toString(startTime);
			String end = Long.toString(endTime);
			String where = KEY_START_TIME + " >= " + start + " and "
					+ KEY_START_TIME + " <= " + end + " or " + KEY_END_TIME
					+ " >= " + start + " and " + KEY_END_TIME + " <= " + end;
			String orderBy = KEY_START_TIME;
			String[] columns = new String[] { KEY_FILE_NAME };
			Cursor cursor = db.query(DATABASE_TABLE, columns, where, null,
					null, null, orderBy);

			// check that cursor isn't empty
			if (cursor.moveToFirst()) {
				// iterate over cursor
				do {
					entries.add(cursor.getString(0)); // <--- depends on what
					// was put in the []
					// columns
				} while (cursor.moveToNext());
			}
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
			}
			return entries;
		}
	}

	public List<String> selectAll() {
		List<String> list = new ArrayList<String>();
		if (!db.isOpen()) {
			Log.d(TAG, "database not open for selectAll");
			return list;
		}

		Cursor cursor = this.db.query(DATABASE_TABLE,
				new String[] { KEY_FILE_NAME }, null, null, null, null, null);
		if (cursor.moveToFirst()) {
			do {
				list.add(cursor.getString(0));
			} while (cursor.moveToNext());
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return list;
	}

	/**
	 * 
	 * @param fileName
	 * @return Number of rows affected.
	 */
	public int delete(String fileName) {
		String where = KEY_FILE_NAME + "='" + fileName + "'";
		int numDeleted = 0;
		if (!db.isOpen()) {
			Log.d("TAG", "database not open to delete");
		} else {
			numDeleted = db.delete(DATABASE_TABLE, where, null);
		}
		return numDeleted;
	}

	/**
	 * Get the names of the oldest files in the database
	 * 
	 * @param n
	 *            Maximum number of files to return
	 * @return List of file names
	 */
	public List<String> getOldestLogfiles(int n) {
		String orderBy = KEY_START_TIME + " desc"; // sort in descending order
		String[] columns = new String[] { KEY_FILE_NAME };
		ArrayList<String> fileNames = new ArrayList<String>();

		if (!db.isOpen()) {
			Log.d(TAG, "database not open for getOldestLogfiles");
			return fileNames;
		}

		Cursor cursor = db.query(DATABASE_TABLE, columns, null, null, null,
				null, orderBy);

		// check that cursor isn't empty
		if (cursor.moveToFirst() && (n > 0)) {
			// iterate over cursor
			do {
				String name = cursor.getString(0); // <--- depends on what
				// was put in the []
				// columns
				fileNames.add(name);
			} while (cursor.moveToNext() && fileNames.size() < n);
		}
		if (cursor != null && !cursor.isClosed()) {
			cursor.close();
		}
		return fileNames;
	}

	/**
	 * 
	 * @author mfaulk
	 * 
	 */
	private static class myDbHelper extends SQLiteOpenHelper {
		public myDbHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
		}

		/*
		 * Called when no database exists in disk and the helper class needs to
		 * create a new one.
		 */
		@Override
		public void onCreate(SQLiteDatabase _db) {
			Log.v(TAG, "myDbHelper onCreate");
			_db.execSQL(DATABASE_CREATE);
		}

		/*
		 * Called when there is a database version mismatch meaning that the
		 * version of the database on disk needs to be upgraded to the current
		 * version.
		 */
		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			// Log the version upgrade.
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion
					+ " to " + _newVersion
					+ ", which will destroy all old data");

			/*
			 * Upgrade the existing database to conform to the new version.
			 * Multiple previous versions can be handled by comparing
			 * _oldVersion and new Version values. The simplest case is to drop
			 * the old table and create a new one.
			 */
			_db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);
			// Create a new one.
			onCreate(_db);
		}
	}
}
